WEBVTT 00:00.590 --> 00:07.100 Better come to the solution for coding exercise 30 and then to import panels as an umpire and see porn 00:08.000 --> 00:15.740 and we import the Dow Jones Industrial Average index price data and we create a daytime index and safer 00:15.820 --> 00:23.480 the sliding data frame and the rabbit Dow Jones so let's inspect here the Dow Jones index price data 00:26.500 --> 00:33.070 and actually the Dow Jones index also stock market index that indicates that the value of 30 large U.S. 00:33.070 --> 00:41.080 companies and these 30 companies are also included in the S&P 500 index and actually the constituents 00:41.080 --> 00:42.050 are price weighted. 00:42.070 --> 00:49.030 So we will see this in the next section but uh first of all we are only interested here in the close 00:49.030 --> 00:56.640 price and therefore we selected the column close and uh we create a data frame instead of having a panel 00:56.650 --> 00:59.620 series so we overwrite. 00:59.650 --> 01:08.410 The variable down tones and then we create the simple moving average 50 year with a window of 50 and 01:08.470 --> 01:15.960 add another column here to our data frame and we can do this by selecting the close price and use the 01:15.970 --> 01:20.340 rolling method and we have a rolling window of 50. 01:20.680 --> 01:27.250 And finally we are actually interested in the mean or the average of the last 50 days. 01:27.610 --> 01:36.560 So we change the mean method so here we have added the new column simple moving average 50 and the same 01:36.560 --> 01:39.660 we can also do for the simple moving average 200. 01:39.670 --> 01:41.890 With a window of 200. 01:43.070 --> 01:45.850 So here we have the close price. 01:45.860 --> 01:48.890 The simple moving average 50 and 200. 01:49.190 --> 01:58.060 And we can also visualize those three time series with the plot method so uh we we have the close price 01:58.120 --> 01:59.360 in blue. 01:59.560 --> 02:09.140 The simple moving average 50 and green and 200 in red and they're actually trading strategies based 02:09.140 --> 02:11.230 on simple moving averages. 02:11.300 --> 02:18.980 So the general rule is that whenever the simple moving average with the smaller window as above the 02:18.980 --> 02:22.490 simple moving average with the larger window. 02:22.820 --> 02:29.510 So whenever these simple moving average 50 is above these simple moving average 200 then this could 02:29.510 --> 02:37.750 be a sign to purchase or to buy the index or to invest into the index and the rationale behind this 02:37.870 --> 02:44.400 strategy is that the most recent price trend will persist in the near future. 02:44.470 --> 02:52.990 And now let's assume we are at the 15th of May to 17 and we shall verify whether a trader with a simple 02:52.990 --> 03:00.320 moving average momentum strategy was invested in the Dow Jones said to this day and therefore we uh 03:00.340 --> 03:09.960 select uh the respective day and we can see that the average index price of value for the immediately 03:09.990 --> 03:16.040 preceding 50 days was at twenty thousand seven hundred and the SMI. 03:16.050 --> 03:18.870 Two hundred is nineteen thousand. 03:18.870 --> 03:24.660 So the shorter simple moving average is above the longer simple moving average. 03:24.660 --> 03:33.200 And this could be a sign to invest in to the Dow Jones index at this stay here. 03:33.350 --> 03:38.290 Next we have to convert the Dow Jones so closing prices to a monthly frequency. 03:38.750 --> 03:45.850 And of course we can do this with the free sample method and the we pass a period to the kind parameter 03:45.890 --> 03:48.180 and we select for each and every month. 03:48.200 --> 03:50.510 The very last trading price. 03:50.510 --> 03:58.720 And also here this is a panel series and we transform this to a data frame and we save it here in the 03:58.720 --> 04:07.610 variable Dow Jones monthly so here we have a monthly price data and in the investment and funds industry. 04:07.700 --> 04:17.090 It is common practice to report past performance based on monthly returns and typically based on the 04:17.150 --> 04:19.910 last 36 months. 04:19.970 --> 04:27.050 So we calculate your monthly returns by using the percentage change method and to be at year another 04:27.050 --> 04:34.440 column return so here we have monthly prices and monthly returns. 04:34.710 --> 04:39.240 And then we can also calculate the 36 month rolling mean return. 04:39.240 --> 04:46.590 And we can add another column here and we can simply do this by selecting the return column and change 04:46.590 --> 04:56.840 the rolling method with a window of 36 months and calculate the mean so that's the rolling mean return 04:56.840 --> 04:57.230 here. 04:58.270 --> 05:00.240 And the same we can also do for the risk. 05:00.250 --> 05:05.980 So we can calculate the 36 months a rolling standard deviation of returns. 05:05.980 --> 05:14.120 And again we select here the returns to the column and say in the rolling method with a window of 36. 05:14.260 --> 05:19.330 And then we calculate the standard deviation. 05:19.330 --> 05:27.380 So here we have a rolling risk and return for the Dow Jones based on monthly return data and also here 05:27.430 --> 05:32.080 might be more attractive to annualize the risk and return. 05:32.080 --> 05:40.180 And we can annualized the return by multiplying the return with twelve and the same we can do with risk 05:40.210 --> 05:46.780 and standard deviation units by multiplying it with the square root of twelve. 05:46.840 --> 05:49.050 So this is nothing new. 05:49.090 --> 05:53.350 And here we have for the annualized rolling risk and rolling return. 05:53.350 --> 05:56.190 And we can also plot both. 05:56.290 --> 06:00.430 So we selected the last two columns and use the plot method 06:03.150 --> 06:10.090 and here we have and blue the rolling mean return and in green the rolling standard deviation of returns. 06:10.290 --> 06:18.210 And actually here we can see that whenever the risk increases the return decreases and vice versa. 06:18.210 --> 06:23.090 So it seems like there's a negative correlation between risk and return. 06:24.760 --> 06:31.370 Next we shall import the column state and close for the S&P 500 Total Return Index. 06:31.480 --> 06:37.930 And as you might recall in the video lectures we worked with the S&P 500 price return index that does 06:37.960 --> 06:39.880 not include dividends. 06:39.880 --> 06:48.490 So here we are working with the Total Return Index and we import the CSP file and also here we transform 06:48.940 --> 06:52.830 the column with data information to daytime index. 06:53.200 --> 06:56.410 And we only use the columns that date and close 06:59.500 --> 07:02.280 so that's the total return price data. 07:05.330 --> 07:12.590 And we can also down sample the price data with the free sample method to annual frequency and we select 07:12.710 --> 07:17.170 the very last the trading price of each year. 07:17.360 --> 07:24.640 So we we have annual price data starting from nineteen hundred eighty eight and our plan planets to 07:24.640 --> 07:33.190 create the return triangle for the S&P 500 and therefore first of all we calculate lucky returns. 07:33.190 --> 07:41.470 So in this case it's better to have a lock returns than simple returns and we can do this by dividing 07:41.470 --> 07:48.010 each and every price by the immediately preceding price of the preceding year. 07:48.130 --> 07:53.820 And we use here the end Peter lock method and be safe actually. 07:53.940 --> 07:57.030 The new data frame and the variable annual returns. 07:57.690 --> 08:00.270 And actually we rename the column label to return. 08:00.270 --> 08:01.830 So that makes more sense. 08:02.460 --> 08:06.810 So here we have the annual returns annual lock returns. 08:06.810 --> 08:11.130 And actually we are starting at the return for the year nineteen hundred eighty nine. 08:11.130 --> 08:17.700 So we have a period of 30 years and therefore offers a five week waiting list starting with a three 08:17.860 --> 08:19.290 to one. 08:19.350 --> 08:22.940 So we save the list here in the viable windows. 08:23.010 --> 08:31.320 This is a list comprehension here and then we read the 30 new columns and each column stands for a rolling 08:31.320 --> 08:35.580 return with a window from 30 years to one year. 08:35.910 --> 08:42.950 And we can do this year with a for loop and uh for example let's take the rolling window of 10 and we 08:42.980 --> 08:50.640 create here the column 10 years and then we take the return column and calculate the rolling mean return 08:50.640 --> 08:59.920 for the last the immediately preceding 10 years so let's have a look and here we have uh the 30 new 08:59.920 --> 09:03.550 columns from 30 years that the one year 09:07.360 --> 09:09.690 and to create the triangle data frame. 09:09.700 --> 09:12.700 We simply have to drop the returns column. 09:12.730 --> 09:15.980 And of course we can do this with the drop method. 09:16.360 --> 09:19.520 And that's our final triangle data frame. 09:19.780 --> 09:26.620 And then we can pass up the triangle data frame to seaborne heat map and we use a red yellow green color 09:26.620 --> 09:35.170 map and we determine that we want to have yellow color at a mean lock return of 0 and a deep red color 09:35.200 --> 09:43.330 at an average lock return of minus 10 percent and a deep green color at a average return of 15 percent. 09:43.420 --> 09:44.530 So let's have a look here. 09:48.450 --> 09:54.970 So that's uh the return triangle for the S&P 500 Total Return Index. 09:54.970 --> 10:01.800 And the question is here based on historical data are there any time periods of eleven years or longer 10:01.800 --> 10:06.450 where investors lost money and let's have a look here. 10:06.480 --> 10:16.050 So here we have investment periods of 10 years and TBA obviously a negative mean return where investors 10:16.140 --> 10:20.520 lost money but then starting with eleven years and so on. 10:20.550 --> 10:27.360 There's actually no historical case where investors lost the money with the S&P 500 index 10:30.220 --> 10:37.060 and finally here we import monthly returns for the S&P 500 and the Dow Jones. 10:37.060 --> 10:39.790 So both are here price return index 10:43.140 --> 10:49.320 so we have two columns the S&P 500 and Dow Jones and these are monthly returns starting from nineteen 10:49.320 --> 10:56.000 hundred ninety and before we have calculated rolling returns and rolling risk of one index. 10:56.130 --> 11:00.190 But we can also calculate the rolling correlation between the two indexes. 11:00.180 --> 11:03.960 So for example the S&P 500 and the Dow Jones. 11:04.320 --> 11:12.970 So it's pretty unlikely that the correlation between those two will remain constant over time and actually 11:12.970 --> 11:20.470 we can simply do this by selecting one index so for example the S&P 500 index then using the rolling 11:20.470 --> 11:28.600 method with a window of thirty six and then we change the call method and TV pass that the other index. 11:28.600 --> 11:30.900 So here the Dow Jones index. 11:31.240 --> 11:36.600 And by doing so we are creating the rolling correlation and we save the you're all in correlation and 11:36.600 --> 11:39.200 the variable a rolling correlation. 11:39.700 --> 11:44.170 And let's have a look at the last the 15 timestamps. 11:44.170 --> 11:46.660 And here we have the rolling correlation. 11:46.660 --> 11:49.470 For the last the 15 months. 11:49.480 --> 11:58.160 And we can also plot the rolling in correlation so it's actually no surprise that the S&P 500 and the 11:58.160 --> 12:04.970 Dow Jones Index are highly positively correlated because actually the constituents of the cadaver Jones 12:04.970 --> 12:13.820 index I included also in the S&P 500 index and the still the correlation is not a perfectly stable over 12:13.820 --> 12:14.610 time. 12:14.750 --> 12:21.840 So we have uh 0 point 9 8 but we also had 0 point 2 8 6. 12:21.840 --> 12:28.220 And with this we are finishing with coding exercise 30 and I hope we had fun and uh looking forward 12:28.220 --> 12:30.650 to seeing you also in the next exercise by.